﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;

namespace DataAdapter与DataSet的使用
{
    public partial class CRUD : Page
    {
        private readonly SqlConnection connection = new SqlConnection();
        private readonly SqlDataAdapter dataAdapter = new SqlDataAdapter();
        private readonly DataSet dataSet = new DataSet();

        protected void Page_Load(object sender, EventArgs e)
        {
            InitializeDataAdapter();
            ShowData();
        }

        public void InitializeDataAdapter()
        {
            connection.ConnectionString = "data source=10.22.149.99;database=School;uid=Student;pwd=123456";

            var selectCommand = new SqlCommand("SELECT * FROM Student", connection);
            dataAdapter.SelectCommand = selectCommand;

            var builder = new SqlCommandBuilder(dataAdapter);
            dataAdapter.DeleteCommand = builder.GetDeleteCommand();
            dataAdapter.UpdateCommand = builder.GetUpdateCommand();
            dataAdapter.InsertCommand = builder.GetInsertCommand();
        }

        public void ShowData()
        {
            dataAdapter.Fill(dataSet);
            gridview1.DataSource = dataSet;
            gridview1.DataBind();
        }

        public void DeleteData()
        {
            int lastRecordIndex = dataSet.Tables[0].Rows.Count - 1;
            dataSet.Tables[0].Rows[lastRecordIndex].Delete();
            dataAdapter.Update(dataSet);
        }

        public void InsertData()
        {
            DataRow row = dataSet.Tables[0].NewRow();
            row["StudentID"] = (new Random()).Next(95001, 95999);
            row["Name"] = "Alex";
            row["Gender"] = "男";
            row["DayOfBirth"] = "1992-12-6";
            row["Address"] = "宁波市江北区庄市街道2021号";
            row["Department"] = "软件工程";
            dataSet.Tables[0].Rows.InsertAt(row, dataSet.Tables[0].Rows.Count);
            dataAdapter.Update(dataSet);
        }

        public void UpdateData()
        {
            dataSet.Tables[0].Rows[0]["Name"] = "Jane";
            dataAdapter.Update(dataSet);
        }

        protected void btnDelete_Click(object sender, EventArgs e)
        {
            DeleteData();
            gridview1.DataBind();
        }

        protected void btnInsert_Click(object sender, EventArgs e)
        {
            InsertData();
            gridview1.DataBind();
        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            UpdateData();
            gridview1.DataBind();
        }
    }
}